USE [CFI]
GO
/****** Object:  StoredProcedure [dbo].[Usp_DeleteUserIdeaApplication]    Script Date: 01/25/2012 00:24:50 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_DeleteUserIdeaApplication]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Usp_DeleteUserIdeaApplication]
GO
/****** Object:  StoredProcedure [dbo].[Usp_GetUserIdeaApplication]    Script Date: 01/25/2012 00:24:50 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_GetUserIdeaApplication]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Usp_GetUserIdeaApplication]
GO
/****** Object:  StoredProcedure [dbo].[Usp_GetUserIdeaApplicationByID]    Script Date: 01/25/2012 00:24:50 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_GetUserIdeaApplicationByID]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Usp_GetUserIdeaApplicationByID]
GO
/****** Object:  StoredProcedure [dbo].[Usp_InsertUserIdeaApplication]    Script Date: 01/25/2012 00:24:50 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_InsertUserIdeaApplication]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Usp_InsertUserIdeaApplication]
GO
/****** Object:  StoredProcedure [dbo].[Usp_UpdateUserIdeaApplication]    Script Date: 01/25/2012 00:24:50 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_UpdateUserIdeaApplication]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Usp_UpdateUserIdeaApplication]
GO
/****** Object:  StoredProcedure [dbo].[Usp_DeleteUserIdeaList]    Script Date: 01/25/2012 00:24:50 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_DeleteUserIdeaList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Usp_DeleteUserIdeaList]
GO
/****** Object:  StoredProcedure [dbo].[Usp_GetUserIdeaList]    Script Date: 01/25/2012 00:24:50 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_GetUserIdeaList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Usp_GetUserIdeaList]
GO
/****** Object:  StoredProcedure [dbo].[Usp_InsertUserIdeaList]    Script Date: 01/25/2012 00:24:50 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_InsertUserIdeaList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Usp_InsertUserIdeaList]
GO
/****** Object:  StoredProcedure [dbo].[Usp_InsertUserIdeaList]    Script Date: 01/25/2012 00:24:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_InsertUserIdeaList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Usp_InsertUserIdeaList]
	(
		@user		nvarchar(32),
		@ideaId		int
	)
AS
	IF NOT EXISTS (SELECT * FROM UserIdeaList WHERE [User] = @user AND IdeaID = @ideaID)
		INSERT INTO UserIdeaList
					([User], IdeaID)
		VALUES      (@user,@ideaId)

	RETURN
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Usp_GetUserIdeaList]    Script Date: 01/25/2012 00:24:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_GetUserIdeaList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Usp_GetUserIdeaList]
	(
		@user		nvarchar(32),
		@ideaId		int
	)
AS
	SELECT      Count(*)
	FROM        UserIdeaList
	WHERE       ([User] = @user) AND (IdeaID = @ideaId)

	RETURN
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Usp_DeleteUserIdeaList]    Script Date: 01/25/2012 00:24:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_DeleteUserIdeaList]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Usp_DeleteUserIdeaList]
	(
		@user		nvarchar(32),
		@ideaId		int
	)
AS
	DELETE FROM UserIdeaList
	WHERE [User] = @user AND IdeaID = @ideaID

	RETURN
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Usp_UpdateUserIdeaApplication]    Script Date: 01/25/2012 00:24:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_UpdateUserIdeaApplication]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Usp_UpdateUserIdeaApplication]
	(
		@id				int,
		@status			int,
		@approver		nvarchar(32),
		@approveReason	nvarchar(500)
	)
AS
	UPDATE      UserIdeaApplication
	SET         Status = @status, Approver = @approver, ApproveReason = @approveReason
	WHERE		ID = @id

	RETURN
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Usp_InsertUserIdeaApplication]    Script Date: 01/25/2012 00:24:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_InsertUserIdeaApplication]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Usp_InsertUserIdeaApplication]
	(
		@applicant		nvarchar(32),
		@ideaID			int,
		@appTime		datetime,
		@appReason		nvarchar(500),
		@status			int
	)
AS
	INSERT INTO UserIdeaApplication
	            (Applicant, IdeaID, AppTime, AppReason, Status, Approver, ApproveReason)
	VALUES      (@applicant,@ideaID,@appTime,@appReason,@status,NULL,NULL)

	RETURN
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Usp_GetUserIdeaApplicationByID]    Script Date: 01/25/2012 00:24:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_GetUserIdeaApplicationByID]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Usp_GetUserIdeaApplicationByID]
	(
		@id			int
	)
AS
	SELECT      ID, Applicant, IdeaID, AppTime, AppReason, Status, Approver, ApproveReason
	FROM        UserIdeaApplication
	WHERE		ID = @id

	RETURN
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Usp_GetUserIdeaApplication]    Script Date: 01/25/2012 00:24:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_GetUserIdeaApplication]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Usp_GetUserIdeaApplication]
	(
		@applicant		nvarchar(32),
		@ideaID			int,
		@approver		nvarchar(32),
		@timeF			datetime,
		@timeT			datetime,
		@status			int
	)
AS
	SELECT      ID, Applicant, IdeaID, AppTime, AppReason, Status, Approver, ApproveReason
	FROM        UserIdeaApplication
	WHERE		(@applicant IS NULL OR Applicant = @applicant) AND
				(@ideaID IS NULL OR IdeaID = @ideaID) AND
				(@approver IS NULL OR Approver = @approver) AND
				(@timeF IS NULL OR AppTime >= @timeF) AND
				(@timeT IS NULL OR AppTime <= @timeT) AND
				(@status IS NULL OR Status = @status)
	ORDER BY	ID

	RETURN
' 
END
GO
/****** Object:  StoredProcedure [dbo].[Usp_DeleteUserIdeaApplication]    Script Date: 01/25/2012 00:24:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Usp_DeleteUserIdeaApplication]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Usp_DeleteUserIdeaApplication]
	(
		@id				int
	)
AS
	DELETE FROM UserIdeaApplication
	WHERE       (ID = @id)

	RETURN
' 
END
GO
